package cn.qingxuntech.console.service

import cn.hutool.core.date.DateUtil
import cn.hutool.core.util.NumberUtil
import cn.hutool.core.util.StrUtil
import cn.qingxuntech.console.config.QingXunTechProperties
import cn.qingxuntech.console.entity.CalcDayReport
import cn.qingxuntech.console.entity.CalcHourReport
import cn.qingxuntech.console.entity.SspSpace
import cn.qingxuntech.console.enumration.BidFlag
import cn.qingxuntech.console.enumration.EventType
import cn.qingxuntech.console.mapper.CalcDayReportMapper
import cn.qingxuntech.console.mapper.CalcHourReportMapper
import cn.qingxuntech.console.mapper.SspSpaceMapper
import com.aliyun.odps.Odps
import com.aliyun.odps.task.SQLTask
import com.mybatisflex.core.query.QueryWrapper
import com.mybatisflex.kotlin.extensions.db.insert
import com.mybatisflex.kotlin.extensions.db.update
import com.mybatisflex.kotlin.extensions.kproperty.eq
import com.mybatisflex.kotlin.extensions.wrapper.whereWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.scheduling.annotation.Scheduled
import org.springframework.stereotype.Service
import java.time.LocalDateTime

/**
 * @author zooy
 * @since 2025/10/31-16:53
 */
@Service
class ScheduleService {

    @Autowired
    private lateinit var odps: Odps

    @Autowired
    private lateinit var calcHourReportMapper: CalcHourReportMapper

    @Autowired
    private lateinit var calcDayReportMapper: CalcDayReportMapper

    @Autowired
    private lateinit var sspSpaceMapper: SspSpaceMapper

    @Autowired
    private lateinit var qingXunTechProperties: QingXunTechProperties


    /**
     * 小时报表
     * 每8,18,28,38,48,58分钟执行一次
     */
    @Scheduled(cron = "0 8,18,28,38,48,58 * * * ?")
    fun hourReport() {

        if (qingXunTechProperties.environment != "prod") {
            return
        }

        val now = LocalDateTime.now()
        if (now.minute == 8) {
            // 需要统计上一个小时的数据
            val prevNow = now.minusHours(1)
            val prevDt = DateUtil.format(prevNow, "yyyyMMdd")
            val prevHour = DateUtil.format(prevNow, "HH")
            doHourReport(prevDt, prevHour)
        }
        // 处理当前小时的数据
        val dt = DateUtil.format(now, "yyyyMMdd")
        val hour = DateUtil.format(now, "HH")
        doHourReport(dt, hour)

    }

    /**
     * 天报表,每个小时统计一次
     */

    @Scheduled(cron = "0 10 * * * ?")
    fun dayReport() {

        val now = LocalDateTime.now()
        // 处理前一天的数据
        if (now.hour == 0) {
            val prevNow = now.minusDays(1)
            val dt = DateUtil.format(prevNow, "yyyyMMdd")
            doDayReport(dt)
        }
        val dt = DateUtil.format(now, "yyyyMMdd")
        doDayReport(dt)
    }

    fun doHourReport(dt: String, hour: String) {
        // 获取请求数据
        val run = SQLTask.run(
            odps, StrUtil.format(
                """
            SELECT  adspaceid,
                    configid,
                    SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS requests,
                    SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END) AS forwards,
                    SUM(CASE WHEN status = '204' THEN 1 ELSE 0 END) AS noads,
                    SUM(CASE WHEN status = '230' THEN 1 ELSE 0 END) AS lowprices,
                    SUM(CASE WHEN status = '400' THEN 1 ELSE 0 END) AS errors,
                    SUM(CASE WHEN status = '429' THEN 1 ELSE 0 END) AS limits,
                    SUM(CASE WHEN status = '430' THEN 1 ELSE 0 END) AS timeouts,
                    SUM(price) AS prices,
                    SUM(rprice) AS rprices
            FROM    sdk_ad_log
            WHERE   dt = '{}'
            AND     hour = '{}'
            GROUP BY adspaceid, configid;
        """.trimIndent(), dt, hour
            )
        )
        run.waitForSuccess()

        val records = SQLTask.getResult(run)

        for (record in records) {
            val adspaceId = record["adspaceid"].toString()
            val conid = record["configid"].toString().toInt()
            // 获取媒体id
            val mediaId = sspSpaceMapper.selectOneByQuery(
                QueryWrapper.create()
                    .eq(SspSpace::id, adspaceId)
            )?.mediaId

            val calcHourReport = calcHourReportMapper.selectOneByQuery(
                QueryWrapper.create()
                    .eq(CalcHourReport::adspaceId, adspaceId)
                    .eq(CalcHourReport::conid, conid)
                    .eq(CalcHourReport::dt, dt)
                    .eq(CalcHourReport::hour, hour)
            )
            if (calcHourReport == null) {
                // 插入数据库表中，进行分析
                insert(CalcHourReport().also {
                    it.adspaceId = record["adspaceid"].toString()
                    it.conid = conid
                    it.mediaId = mediaId
                    it.dt = dt
                    it.hour = hour
                    it.requests = record["requests"].toString().toLong()
                    it.forwards = record["forwards"].toString().toLong()
                    it.noAds = record["noads"].toString().toLong()
                    it.lowPrices = record["lowprices"].toString().toLong()
                    it.errors = record["errors"].toString().toLong()
                    it.limits = record["limits"].toString().toLong()
                    it.timeouts = record["timeouts"].toString().toLong()
                    it.prices = record["prices"].toString().toLong()
                    it.rprices = record["rprices"].toString().toLong()
                    it.createTime = LocalDateTime.now()
                })
            } else {
                // 更新数据库表中，进行分析
                update<CalcHourReport> {
                    CalcHourReport::requests set record["requests"].toString().toLong()
                    CalcHourReport::forwards set record["forwards"].toString().toLong()
                    CalcHourReport::noAds set record["noads"].toString().toLong()
                    CalcHourReport::lowPrices set record["lowprices"].toString().toLong()
                    CalcHourReport::errors set record["errors"].toString().toLong()
                    CalcHourReport::limits set record["limits"].toString().toLong()
                    CalcHourReport::timeouts set record["timeouts"].toString().toLong()
                    CalcHourReport::prices set record["prices"].toString().toDouble()
                    CalcHourReport::rprices set record["rprices"].toString().toDouble()
                    whereWith {
                        (CalcHourReport::adspaceId eq adspaceId)
                            .and(CalcHourReport::conid eq conid)
                            .and(CalcHourReport::dt eq dt)
                            .and(CalcHourReport::hour eq hour)
                    }
                }
            }
        }

        doHourEventReport(dt, hour)
    }

    fun doDayReport(dt: String) {
        // 获取请求数据
        val run = SQLTask.run(
            odps, StrUtil.format(
                """
            SELECT  adspaceid,
                    configid,
                    SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS requests,
                    SUM(CASE WHEN status = '1' THEN 1 ELSE 0 END) AS forwards,
                    SUM(CASE WHEN status = '204' THEN 1 ELSE 0 END) AS noads,
                    SUM(CASE WHEN status = '230' THEN 1 ELSE 0 END) AS lowprices,
                    SUM(CASE WHEN status = '400' THEN 1 ELSE 0 END) AS errors,
                    SUM(CASE WHEN status = '429' THEN 1 ELSE 0 END) AS limits,
                    SUM(CASE WHEN status = '430' THEN 1 ELSE 0 END) AS timeouts,
                    SUM(price) AS prices,
                    SUM(rprice) AS rprices
            FROM    sdk_ad_log
            WHERE   dt = '{}'
            GROUP BY adspaceid, configid;
        """.trimIndent(), dt
            )
        )
        run.waitForSuccess()

        val records = SQLTask.getResult(run)

        for (record in records) {
            val adspaceId = record["adspaceid"].toString()
            val conid = record["configid"].toString().toInt()
            // 获取媒体id
            val mediaId = sspSpaceMapper.selectOneByQuery(
                QueryWrapper.create()
                    .eq(SspSpace::id, adspaceId)
            )?.mediaId

            val calcDayReport = calcDayReportMapper.selectOneByQuery(
                QueryWrapper.create()
                    .eq(CalcDayReport::adspaceId, adspaceId)
                    .eq(CalcDayReport::conid, conid)
                    .eq(CalcDayReport::dt, dt)
            )
            if (calcDayReport == null) {
                // 插入数据库表中，进行分析
                insert(CalcDayReport().also {
                    it.adspaceId = record["adspaceid"].toString()
                    it.conid = conid
                    it.mediaId = mediaId
                    it.dt = dt
                    it.requests = record["requests"].toString().toLong()
                    it.forwards = record["forwards"].toString().toLong()
                    it.noAds = record["noads"].toString().toLong()
                    it.lowPrices = record["lowprices"].toString().toLong()
                    it.errors = record["errors"].toString().toLong()
                    it.limits = record["limits"].toString().toLong()
                    it.timeouts = record["timeouts"].toString().toLong()
                    it.prices = record["prices"].toString().toLong()
                    it.rprices = record["rprices"].toString().toLong()
                    it.createTime = LocalDateTime.now()
                })
            } else {
                // 更新数据库表中，进行分析
                update<CalcDayReport> {
                    CalcDayReport::requests set record["requests"].toString().toLong()
                    CalcDayReport::forwards set record["forwards"].toString().toLong()
                    CalcDayReport::noAds set record["noads"].toString().toLong()
                    CalcDayReport::lowPrices set record["lowprices"].toString().toLong()
                    CalcDayReport::errors set record["errors"].toString().toLong()
                    CalcDayReport::limits set record["limits"].toString().toLong()
                    CalcDayReport::timeouts set record["timeouts"].toString().toLong()
                    CalcDayReport::prices set record["prices"].toString().toDouble()
                    CalcDayReport::rprices set record["rprices"].toString().toDouble()
                    whereWith {
                        (CalcDayReport::adspaceId eq adspaceId)
                            .and(CalcDayReport::conid eq conid)
                            .and(CalcDayReport::dt eq dt)
                    }
                }
            }
        }

        doDayEventReport(dt)
    }

    fun doHourEventReport(dt: String, hour: String) {
        // 获取请求数据
        val run = SQLTask.run(
            odps, StrUtil.format(
                """
                SELECT  aid
                        ,conid
                        ,type
                        ,bidflag
                        ,COUNT(1) cc
                        ,SUM(price) price
                        ,SUM(mincome) mincome
                FROM    ad_event_log
                WHERE   dt = '{}'
                AND     hour = '{}'
                GROUP BY aid
                         ,conid
                         ,type
                         ,bidflag
                ;
            """.trimIndent(), dt, hour
            )
        )
        run.waitForSuccess()
        val records = SQLTask.getResult(run)

        for (record in records) {
            val aid = record["aid"].toString()
            val conid = record["conid"].toString().toInt()
            val type = record["type"].toString().toInt()
            val bidFlagStr = record.get("bidflag")
            val bidFlag = if (bidFlagStr.equals("\\N")) BidFlag.NO.code else bidFlagStr.toString().toInt()
            val cc = record["cc"].toString().toLong()
            val priceStr = record.get("price")
            val price = if (priceStr.equals("\\N")) 0.0 else NumberUtil.parseLong(priceStr.toString())
            val mincomeStr = record.get("mincome")
            val mincome = if (mincomeStr.equals("\\N")) 0.0 else NumberUtil.parseLong(mincomeStr.toString())

            if (type == EventType.IMPRESSION.code) {
                // 更新数据库
                update<CalcHourReport> {
                    CalcHourReport::imps set cc
                    CalcHourReport::iprices set price
                    CalcHourReport::mincomes set mincome
                    whereWith {
                        (CalcHourReport::adspaceId eq aid)
                            .and(CalcHourReport::conid eq conid)
                            .and(CalcHourReport::dt eq dt)
                            .and(CalcHourReport::hour eq hour)
                    }
                }
            } else if (type == EventType.CLICK.code) {
                // 更新数据库
                update<CalcHourReport> {
                    CalcHourReport::clicks set cc
                    whereWith {
                        (CalcHourReport::adspaceId eq aid)
                            .and(CalcHourReport::conid eq conid)
                            .and(CalcHourReport::dt eq dt)
                            .and(CalcHourReport::hour eq hour)
                    }
                }
            } else if (type == EventType.WIN_PRICE.code) {

                if (bidFlag == BidFlag.YES.code) {
                    // 更新数据库
                    update<CalcHourReport> {
                        CalcHourReport::wins set cc
                        whereWith {
                            (CalcHourReport::adspaceId eq aid)
                                .and(CalcHourReport::conid eq conid)
                                .and(CalcHourReport::dt eq dt)
                                .and(CalcHourReport::hour eq hour)
                        }
                    }
                } else if (bidFlag == BidFlag.NO.code) {
                    // 更新数据库
                    update<CalcHourReport> {
                        CalcHourReport::fails set cc
                        whereWith {
                            (CalcHourReport::adspaceId eq aid)
                                .and(CalcHourReport::conid eq conid)
                                .and(CalcHourReport::dt eq dt)
                                .and(CalcHourReport::hour eq hour)
                        }
                    }
                }
            }

        }
    }

    fun doDayEventReport(dt: String) {
        // 获取请求数据
        val run = SQLTask.run(
            odps, StrUtil.format(
                """
                SELECT  aid
                        ,conid
                        ,type
                        ,bidflag
                        ,COUNT(1) cc
                        ,SUM(price) price
                        ,SUM(mincome) mincome
                FROM    ad_event_log
                WHERE   dt = '{}'
                GROUP BY aid
                         ,conid
                         ,type
                         ,bidflag
                ;
            """.trimIndent(), dt
            )
        )
        run.waitForSuccess()
        val records = SQLTask.getResult(run)

        for (record in records) {
            val aid = record["aid"].toString()
            val conid = record["conid"].toString().toInt()
            val type = record["type"].toString().toInt()
            val bidFlagStr = record.get("bidflag")
            val bidFlag = if (bidFlagStr.equals("\\N")) BidFlag.NO.code else bidFlagStr.toString().toInt()
            val cc = record["cc"].toString().toLong()
            val priceStr = record.get("price")
            val price = if (priceStr.equals("\\N")) 0.0 else NumberUtil.parseLong(priceStr.toString())
            val mincomeStr = record.get("mincome")
            val mincome = if (mincomeStr.equals("\\N")) 0.0 else NumberUtil.parseLong(mincomeStr.toString())

            if (type == EventType.IMPRESSION.code) {
                // 更新数据库
                update<CalcDayReport> {
                    CalcDayReport::imps set cc
                    CalcDayReport::iprices set price
                    CalcDayReport::mincomes set mincome
                    whereWith {
                        (CalcDayReport::adspaceId eq aid)
                            .and(CalcDayReport::conid eq conid)
                            .and(CalcDayReport::dt eq dt)
                    }
                }
            } else if (type == EventType.CLICK.code) {
                // 更新数据库
                update<CalcDayReport> {
                    CalcDayReport::clicks set cc
                    whereWith {
                        (CalcDayReport::adspaceId eq aid)
                            .and(CalcDayReport::conid eq conid)
                            .and(CalcDayReport::dt eq dt)
                    }
                }
            } else if (type == EventType.WIN_PRICE.code) {

                if (bidFlag == BidFlag.YES.code) {
                    // 更新数据库
                    update<CalcDayReport> {
                        CalcDayReport::wins set cc
                        whereWith {
                            (CalcDayReport::adspaceId eq aid)
                                .and(CalcDayReport::conid eq conid)
                                .and(CalcDayReport::dt eq dt)
                        }
                    }
                } else if (bidFlag == BidFlag.NO.code) {
                    // 更新数据库
                    update<CalcDayReport> {
                        CalcDayReport::fails set cc
                        whereWith {
                            (CalcDayReport::adspaceId eq aid)
                                .and(CalcDayReport::conid eq conid)
                                .and(CalcDayReport::dt eq dt)
                        }
                    }
                }
            }

        }
    }

}
